Database Design
This design has not been finalized as of yet, as we have yet to determine suitable sizes of VARCHAR fields, (de)normalize appropriately and perform other design verifications. However, it is clear that all given classes are physical classes and not catalogue classes. Keys The diagram does not accurately show the details of the implementation, but a rough layout. Primary Keys *Users are identified by their username *Team Members are identified by their username *Diaries are identified by their author and the date *Comments are identified by the tank name, tank author and user *Tanks are identified by their name and author Foreign Keys *In TeamMembers, field 'team_username' references Users 'username' *In Diary, field 'author' references TeamMembers 'team_username' *In Comments, field 'author' references Users 'username' *In Comments, field 'tank' references Tanks 'tank_name' * In Comments, field 'tankauthor' references Tanks 'author' *In Tank, field 'author' references Users 'username' Required Attributes (not part of keys) * In Comments, the field 'comments_date' is required * In Tank, the field 'uploadDate' is requiredn Tank, the field Relationships between Tables Users may have zero-to-many Comments Users may have zero-to-many Tanks Tanks may have''' zero-to-many''' Comments TeamMembers may have zero-to-many Diaries Users may or may not be a part of TeamMembers NormalizationCategory:Design & Diagrams The level of normalization per table is described below, up to 3NF, as a working list (i.e: tables are listed in the lower forms as well, up to the form they violate): 1NF This is a relational database, therefore all tables are in 1NF. 2NF * 'Diary - '''The author and diary date are both required in order to identify diary entry and diary heading. Diary is in 2NF. * '''Users - '''Non-composite primary key, Users is in 2NF. * '''TeamMembers - '''Non-composite primary key, TeamMembers is in 2NF. * '''Comments - '''Comment date, comment and rating all require the full combination of tank, comment author and tank author to be known, making Comments 2NF. * '''Tank - '''ImageURL is always unique, but is determinable by the primary key, making it fully functional dependent. Upload dates, tank data and tank description are alse determinable by the primary key, making Tank 2NF 3NF * '''Diary - '''The diary entry and heading can't determine other attributes, Diary is in 3NF. * '''Users - '''The user's password, secret question and answer and their status as a developer can not determine other values, Users is in 3NF. * '''TeamMembers - '''The team role can not determine the username, TeamMembers is in 3NF. * '''Comments - '''The comment date, comment and rating are not guaranteed to determine other values, Comments is in 3NF. * '''Tank - '''The tank name and author are jointly responsible for determining any other value. The upload date, tank data, tank description and imageURL are therefore not partially dependent on the primary key, meanng Tank is in 3NF. BCNF *'Diary - 'No alternate candidate keys exist, Diary is in BCNF. *'Users - 'No alternate candidate keys exist, Users is in BCNF. *'TeamMembers - 'In our context, the team role can not be used as a candidate key, therefore TeamMembers is in BCNF. *'Comments - 'Neither the comment data, text, comment date or rating can be used in any combination to form a candidate key, so no other candidate key can exist. Comments is in BCNF. *'Tank - '''Boyce-Codd normal form is violated in Tank, since the imageURL is another candidate key to the primary key, but has not been used. Therefore, Tank is not in BCNF.